package database

import (
	"database/sql"
	"errors"
	"fm/model"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"time"
)

var mDB *sql.DB

const (
	USERNAME = "fm"
	PASSWORD = "fm"
	NETWORK  = "tcp"
	SERVER   = "192.168.101.100"
	PORT     = 3306
	DATABASE = "fm"
)
const MAXUSERID = 1000000000

func init() {
	dsn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s", USERNAME, PASSWORD, NETWORK, SERVER, PORT, DATABASE)
	dB, err := sql.Open("mysql", dsn)
	mDB = dB
	if err != nil {
		log.Println(err.Error())
	}
	mDB.SetConnMaxLifetime(100 * time.Second)
	mDB.SetMaxOpenConns(100)
	mDB.SetMaxIdleConns(16)

}
func GetDB() *sql.DB {
	return mDB
}
func CloseDB() {
	err := mDB.Close()
	if err != nil {
		log.Println(err.Error())
	}
}

/*******************************************************
	数据表accounts的增删改查
 ******************************************************/

//AddAccount 向数据库新增一个账户
func AddAccount(userId int, name string, orgId int, personId int, balance float32) error {
	sqlString := fmt.Sprintf("insert into accounts(user_id,name,org_id,person_id,balance) values(%d,'%s',%d,%d,%.2f);", userId, name, orgId, personId, balance)
	log.Println(sqlString)
	_, err := mDB.Exec(sqlString)
	if err != nil {
		log.Println("add account failed! error:", err)
		return err
	} else {
		log.Println("add account success!")
	}
	return nil
}

//DelAccount 从数据库中删除一个账户（仅仅把账户的deleted字段设为TRUE）
func DelAccount(userId int, accountId int) error {
	sqlString := fmt.Sprintf("update accounts set deleted = 1 where id = %d and user_id =%d", accountId, userId)
	log.Println(sqlString)
	_, err := mDB.Exec(sqlString)
	if err != nil {
		log.Println("Del account failed! error:", err)
		return err
	} else {
		log.Println("Del account success!")
	}
	return nil
}

//QueryAccounts 从数据库中查询一个账户的信息
func QueryAccounts(userId int) ([]model.Account, error) {

	var accounts []model.Account
	row := model.Account{}
	rows, err := mDB.Query("select account_id,user_id,name,ifnull(org_id,0),ifnull(person_id,0),balance,ifnull(deleted,0) from accounts where user_id=?", userId)
	if err != nil {
		log.Println("QueryAccounts():query error:", err)
		return nil, nil
	}
	for rows.Next() {
		err = rows.Scan(&row.AccountId, &row.UserId, &row.Name, &row.OrgId, &row.PersonId, &row.Balance, &row.Deleted)
		if err != nil {
			log.Println("QueryAccounts():error in scan rows:", err)
			return nil, nil
		}
		accounts = append(accounts, row)
	}
	return accounts, nil
}

//UpdateBalance 在数据库中更新一个账户的余额
func UpdateBalance(userId int, accountId int) error {
	sqlString := fmt.Sprintf("update accounts set balance=(select sum(amount) from accounts_log where account_id=%d)where account_id=%d and user_id=%d;", accountId, accountId, userId)
	//log.Println(sqlString)
	_, err := mDB.Exec(sqlString)
	if err != nil {
		log.Println("UpdateBalance(): Update account balance failed! error:", err)
		return err
	} else {
		log.Println("UpdateBalance(): Update account balance success!")
	}
	return nil
}

/*******************************************
	数据表user的增删改查
 ******************************************/

//AddUser 向数据库新增一个用户
func AddUser(user model.User) error {

	if user.UserId < 0 {
		return errors.New("user id error")
	}
	if len(user.UserName) == 0 {
		return errors.New("user name error")
	}
	if len(user.PasswordHash) == 0 {
		return errors.New("user password error")
	}

	var mobile sql.NullString
	if len(user.Mobile) == 0 {
		mobile = sql.NullString{}
	} else {
		mobile = sql.NullString{String: user.Mobile, Valid: true}
	}

	var email sql.NullString
	if len(user.Email) == 0 {
		email = sql.NullString{}
	} else {
		email = sql.NullString{String: user.Email, Valid: true}
	}

	var weixin sql.NullString
	if len(user.Weixin) == 0 {
		weixin = sql.NullString{}
	} else {
		weixin = sql.NullString{String: user.Weixin, Valid: true}
	}

	var qq sql.NullString
	if len(user.QQ) == 0 {
		qq = sql.NullString{}
	} else {
		qq = sql.NullString{String: user.QQ, Valid: true}
	}

	if user.CreatedAt <= 0 {
		user.CreatedAt = 0
	}
	if user.DeletedAt <= 0 {
		user.DeletedAt = 0
	}

	_, err := mDB.Exec("insert into user(user_id,user_name,nick_name,password_hash,mobile,email,weixin,qq,created_at,deleted_at) values(?,?,?,?,?,?,?,?,?,?);",
		user.UserId, user.UserName, user.NickName, user.PasswordHash, mobile, email, weixin, qq, user.CreatedAt, user.DeletedAt)
	if err != nil {
		log.Println("insert a user failed! error:", err)
		return err
	} else {
		log.Println("insert a user success!")
		return nil
	}

}

//DelUser 删除一个用户，只是把该用户的deleted设置为TRUE
func DelUser(userId int) error {
	_, err := mDB.Exec("update user set deleted = 1 where user_id = ?;", userId)
	if err != nil {
		log.Println("del a user failed! error:", err)
		return err
	} else {
		log.Println("delete user success!")
	}
	return nil
}

//QueryUserById 根据用户ID来查找用户信息
func QueryUserById(id int) (model.User, bool) {
	sqlStr := fmt.Sprintf("select user_id,user_name,password_hash,ifnull(mobile,''),ifnull(email,''),ifnull(weixin,''),ifnull(qq,'') from user where user_id = '%d'", id)
	log.Println("QueryByName sql string:", sqlStr)

	row := mDB.QueryRow(sqlStr)
	user := model.User{}
	err := row.Scan(&user.UserId, &user.UserName, &user.PasswordHash, &user.Mobile, &user.Email, &user.Weixin, &user.QQ)
	log.Println(user.UserId, user.UserName, user.Mobile, user.Email)
	if err != nil && err != sql.ErrNoRows {
		log.Printf("Can't Find user,where user_d = %d\n", id)
		return model.User{}, false
	}
	return user, true
}

//QueryUserByName 根据用户名查找用户信息
func QueryUserByName(name string) (model.User, bool) {
	sqlStr := fmt.Sprintf("select user_id,user_name,nick_name,password_hash,ifnull(mobile,''),ifnull(email,''),ifnull(weixin,''),ifnull(qq,'') from user where user_name = '%s'", name)
	log.Println("QueryByName sql string:", sqlStr)

	row := mDB.QueryRow(sqlStr)
	user := model.User{}
	err := row.Scan(&user.UserId, &user.UserName, &user.NickName, &user.PasswordHash, &user.Mobile, &user.Email, &user.Weixin, &user.QQ)
	log.Println(user.UserId, user.UserName, user.NickName, user.Mobile, user.Email)
	if err != nil && err != sql.ErrNoRows {
		log.Printf("Can't Find user,where user_name = %s\n", name)
		return model.User{}, false
	}
	return user, true
}

//QueryUserByMobile 根据手机号查找用户信息
func QueryUserByMobile(mobile string) (model.User, bool) {
	sqlStr := fmt.Sprintf("select user_id,user_name,password_hash,ifnull(mobile,''),ifnull(email,''),ifnull(weixin,''),ifnull(qq,'') from user where user_name = '%s'", mobile)
	log.Println("QueryByName sql string:", sqlStr)

	row := mDB.QueryRow(sqlStr)
	user := model.User{}
	err := row.Scan(&user.UserId, &user.UserName, &user.PasswordHash, &user.Mobile, &user.Email, &user.Weixin, &user.QQ)
	log.Println(user.UserId, user.UserName, user.Mobile, user.Email)
	if err != nil && err != sql.ErrNoRows {
		log.Printf("Can't Find user,where mobile = %s\n", mobile)
		return model.User{}, false
	}
	return user, true
}
func QueryUserByEmail(email string) (model.User, bool) {
	sqlStr := fmt.Sprintf("select user_id,user_name,password_hash,ifnull(mobile,''),ifnull(email,''),ifnull(weixin,''),ifnull(qq,'') from user where user_name = '%s'", email)
	log.Println("QueryByName sql string:", sqlStr)

	row := mDB.QueryRow(sqlStr)
	user := model.User{}
	err := row.Scan(&user.UserId, &user.UserName, &user.PasswordHash, &user.Mobile, &user.Email, &user.Weixin, &user.QQ)
	log.Println(user.UserId, user.UserName, user.Mobile, user.Email)
	if err != nil && err != sql.ErrNoRows {
		log.Printf("Can't Find user,where email = %s\n", email)
		return model.User{}, false
	}
	return user, true
}

func ChangeUserPassword(userId int, passwordHash []byte) bool {
	_, err := mDB.Exec("update user set password_hash=? where user_id = ?", passwordHash, userId)
	if err != nil {
		log.Println("ChangeUserPassword():error in update user password_hash:", err)
		return false
	}
	return true
}
func ChangeUserMobile(userId int, mobile string) bool {
	_, err := mDB.Exec("update user set mobile = ? where user_id = ?", mobile, userId)
	if err != nil {
		log.Println("ChangeUserMobile():error in update user mobile：", err)
		return false
	}
	return true
}
func ChangeUserEmail(userId int, email string) bool {
	_, err := mDB.Exec("update user set email = ? where user_id = ?", email, userId)
	if err != nil {
		log.Println("ChangeUserEmail():error in update user email:", err)
		return false
	}
	return true
}
func ChangeUserNickName(userId int, nickName string) bool {
	_, err := mDB.Exec("update user set nick_name = ? where  user_id = ?", nickName, userId)
	if err != nil {
		log.Println("ChangeUserNickName():error in update user nick name:", err)
		return false
	}
	return true
}
func ChangeUserWeixin(userId int, Weixin string) bool {
	_, err := mDB.Exec("update user set weixin = ? where  user_id = ?", Weixin, userId)
	if err != nil {
		log.Println("ChangeUserNickName():error in update user Weixin:", err)
		return false
	}
	return true
}
func ChangeUserQQ(userId int, QQ string) bool {
	_, err := mDB.Exec("update user set qq = ? where  user_id = ?", QQ, userId)
	if err != nil {
		log.Println("ChangeUserNickName():error in update user QQ:", err)
		return false
	}
	return true
}

/**********************************************
	数据表accounts_log的增删改查
**********************************************/

//AddAccountsLog 增加一条记账
func AddAccountsLog(accountsLog model.AccountsLog) error {

	var summary sql.NullString
	if len(accountsLog.Summary) == 0 {
		summary = sql.NullString{}
	} else {
		summary = sql.NullString{String: accountsLog.Summary, Valid: true}
	}
	var category sql.NullString
	if len(accountsLog.Category) == 0 {
		category = sql.NullString{}
	} else {
		category = sql.NullString{String: accountsLog.Category, Valid: true}
	}
	var categoryId sql.NullInt32
	if accountsLog.CategoryId == 0 {
		categoryId = sql.NullInt32{}
	} else {
		categoryId = sql.NullInt32{Int32: int32(accountsLog.CategoryId), Valid: true}
	}
	var orgId sql.NullInt32
	if accountsLog.OrgId == 0 {
		orgId = sql.NullInt32{}
	} else {
		orgId = sql.NullInt32{Int32: int32(accountsLog.OrgId), Valid: true}
	}
	var personId sql.NullInt32
	if accountsLog.PersonId == 0 {
		personId = sql.NullInt32{}
	} else {
		personId = sql.NullInt32{Int32: int32(accountsLog.PersonId), Valid: true}
	}
	//sqlStr := fmt.Sprintf("insert into accounts_log set user_id = %d, account_id=%d, amount=%.2f, summary='%s', category='%s', category_id=%d, org_id=%d, person_id=%d;",
	//	accounts_log.User_id, accounts_log.Account_id, accounts_log.Amount, summary, accounts_log.Category, accounts_log.Category_id, accounts_log.Org_id, accounts_log.Person_id)
	//log.Println("AddAccountsLog():sql = ", sqlStr)
	_, err := mDB.Exec("insert into accounts_log set user_id = ?,account_id=?,amount=?,summary=?,category=?,category_id=?,org_id=?,person_id=?", accountsLog.UserId,
		accountsLog.AccountId, accountsLog.Amount, summary, category, categoryId, orgId, personId)
	if err != nil {
		log.Println("AddAccountsLog():insert log error:", err)
		return err
	}
	log.Println("AddAccountsLog():insert a log success!")
	//更新账户余额
	err = UpdateBalance(accountsLog.UserId, accountsLog.AccountId)
	if err != nil {
		log.Println("AddAccountsLog():update balance failed! error:", err)
		return err
	}
	return nil
}

//DelAccountsLog 删除一条记账
func DelAccountsLog(userId int, accountId int, logId int) error {
	_, err := mDB.Exec("delete from accounts_log where user_id=? and id = ?", userId, logId)
	if err != nil {
		log.Println("DelAccountsLog():delete a log failed:", err)
		return err
	}
	log.Println("DelAccountsLog():delete a log success!")
	err = UpdateBalance(userId, accountId)
	if err != nil {
		log.Println("DelAccountsLog():update balance failed: ", err)
		return err
	}
	return nil
}

//UpdateAccountsLog 修改一条记账
func UpdateAccountsLog(accountsLog model.AccountsLog, userId int, logId int) error {
	_, err := mDB.Exec("update accounts_log set modifytime=now(),amount =?,summary=?,category=?,category_id=?,org_id=?,person_id=? where id = ? and user_id=?",
		accountsLog.Amount, accountsLog.Summary, accountsLog.Category, accountsLog.CategoryId, accountsLog.OrgId, accountsLog.PersonId, logId, userId)
	if err != nil {
		log.Println("UpdateAccountsLog():update a log failed:", err)
		return err
	}
	log.Println("UpdateAccountsLog():update a log success!")

	return nil
}

//QueryAccountsLog 查询用户账户所有记账条目
func QueryAccountsLog(userId int, accountId int) ([]model.AccountsLog, error) {

	var logs []model.AccountsLog
	row := model.AccountsLog{}
	//row.SetUserId(user_id)
	//row.SetAccountId(account_id)
	//var trade, modify string
	rows, err := mDB.Query("select id,user_id,account_id,tradetime,modifytime,amount,ifnull(summary,''),category,ifnull(category_id,0),ifnull(org_id,0),ifnull(person_id,0) from accounts_log where user_id =? and account_id = ?",
		userId, accountId)
	if err != nil {
		log.Println("QueryAccountsLog(): query rows failed! error: ", err)
		return nil, nil
	}
	for rows.Next() {
		err = rows.Scan(&row.Id, &row.UserId, &row.AccountId, &row.TradeTime, &row.ModifyTime, &row.Amount, &row.Summary, &row.Category, &row.CategoryId, &row.OrgId, &row.PersonId)
		if err != nil {
			log.Println("QueryAccountsLog(): rows.scan error：", err)
			return nil, nil
		}

		//row.Trade_time, err = time.ParseInLocation("2006-01-02 15:04:05", trade, time.Local)
		//if err != nil {
		//	log.Println("QueryAccountsLog(): time.ParseInLocation error:", err)
		//	row.Trade_time = time.Time{}
		//}
		//row.Modify_time, err = time.ParseInLocation("2006-01-02 15:04:05", trade, time.Local)
		//if err != nil {
		//	log.Println("QueryAccountsLog(): time.ParseInLocation error:", err)
		//	row.Modify_time = time.Time{}
		//}

		logs = append(logs, row)
	}
	return logs, nil
}
